// ReaderDAO.java - 修改后的版本（解决Date冲突）
package com.library.dao;

import com.library.entity.Reader;
import com.library.util.DBUtil;
import java.sql.*;  // 保留sql包的导入
import java.util.ArrayList;
import java.util.List;

public class ReaderDAO {

    /**
     * 获取所有读者
     */
    public List<Reader> getAllReaders() {
        List<Reader> readers = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT reader_id, name, gender, phone, email, department, " +
                    "type, register_date, expiry_date, status " +
                    "FROM readers ORDER BY name";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Reader reader = resultSetToReader(rs);
                readers.add(reader);
            }
            System.out.println("从数据库加载 " + readers.size() + " 位读者");
        } catch (SQLException e) {
            System.err.println("获取所有读者失败: " + e.getMessage());
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        return readers;
    }

    /**
     * 根据读者ID获取读者
     */
    public Reader getReaderById(String readerId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT reader_id, name, gender, phone, email, department, " +
                    "type, register_date, expiry_date, status " +
                    "FROM readers WHERE reader_id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, readerId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return resultSetToReader(rs);
            }
        } catch (SQLException e) {
            System.err.println("根据ID获取读者失败: " + e.getMessage());
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        return null;
    }

    /**
     * 搜索读者
     */
    public List<Reader> searchReaders(String keyword) {
        List<Reader> readers = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT reader_id, name, gender, phone, email, department, " +
                    "type, register_date, expiry_date, status " +
                    "FROM readers WHERE name LIKE ? OR reader_id LIKE ? OR department LIKE ? " +
                    "ORDER BY name";
            pstmt = conn.prepareStatement(sql);
            String likeKeyword = "%" + keyword + "%";
            pstmt.setString(1, likeKeyword);
            pstmt.setString(2, likeKeyword);
            pstmt.setString(3, likeKeyword);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Reader reader = resultSetToReader(rs);
                readers.add(reader);
            }
        } catch (SQLException e) {
            System.err.println("搜索读者失败: " + e.getMessage());
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        return readers;
    }

    /**
     * 添加新读者
     */
    public boolean addReader(Reader reader) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "INSERT INTO readers (reader_id, name, gender, phone, email, department, " +
                    "type, register_date, expiry_date, status) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, reader.getReaderId());
            pstmt.setString(2, reader.getName());
            pstmt.setString(3, reader.getGender());
            pstmt.setString(4, reader.getPhone());
            pstmt.setString(5, reader.getEmail());
            pstmt.setString(6, reader.getDepartment());
            pstmt.setString(7, reader.getType().name());
            // 使用完整的限定名 java.sql.Date
            pstmt.setDate(8, java.sql.Date.valueOf(reader.getRegisterDate()));
            pstmt.setDate(9, java.sql.Date.valueOf(reader.getExpiryDate()));
            pstmt.setString(10, reader.getStatus().name());

            int result = pstmt.executeUpdate();
            return result > 0;

        } catch (SQLException e) {
            System.err.println("添加读者失败: " + e.getMessage());
            return false;
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }
    }

    /**
     * 更新读者信息
     */
    public boolean updateReader(Reader reader) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE readers SET name=?, gender=?, phone=?, email=?, department=?, " +
                    "type=?, register_date=?, expiry_date=?, status=? WHERE reader_id=?";

            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, reader.getName());
            pstmt.setString(2, reader.getGender());
            pstmt.setString(3, reader.getPhone());
            pstmt.setString(4, reader.getEmail());
            pstmt.setString(5, reader.getDepartment());
            pstmt.setString(6, reader.getType().name());
            // 使用完整的限定名 java.sql.Date
            pstmt.setDate(7, java.sql.Date.valueOf(reader.getRegisterDate()));
            pstmt.setDate(8, java.sql.Date.valueOf(reader.getExpiryDate()));
            pstmt.setString(9, reader.getStatus().name());
            pstmt.setString(10, reader.getReaderId());

            int result = pstmt.executeUpdate();
            return result > 0;

        } catch (SQLException e) {
            System.err.println("更新读者失败: " + e.getMessage());
            return false;
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }
    }

    /**
     * 删除读者
     */
    public boolean deleteReader(String readerId) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "DELETE FROM readers WHERE reader_id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, readerId);

            int result = pstmt.executeUpdate();
            return result > 0;

        } catch (SQLException e) {
            System.err.println("删除读者失败: " + e.getMessage());
            return false;
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }
    }

    /**
     * 将ResultSet转换为Reader对象
     */
    private Reader resultSetToReader(ResultSet rs) throws SQLException {
        String typeStr = rs.getString("type");
        Reader.ReaderType type;
        try {
            type = Reader.ReaderType.valueOf(typeStr);
        } catch (IllegalArgumentException e) {
            type = Reader.ReaderType.STUDENT;
        }

        Reader reader = new Reader(
                rs.getString("reader_id"),
                rs.getString("name"),
                rs.getString("gender"),
                rs.getString("phone"),
                rs.getString("email"),
                rs.getString("department"),
                type
        );

        // 使用 java.sql.Date 转换为 LocalDate
        reader.setRegisterDate(rs.getDate("register_date").toLocalDate());
        reader.setExpiryDate(rs.getDate("expiry_date").toLocalDate());

        String statusStr = rs.getString("status");
        if (statusStr != null) {
            try {
                reader.setStatus(Reader.ReaderStatus.valueOf(statusStr));
            } catch (IllegalArgumentException e) {
                reader.setStatus(Reader.ReaderStatus.ACTIVE);
            }
        }

        return reader;
    }
}
